package impl;

import dao.ItemDao;
import model.Item;
import util.C3P0Util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ItemDaoImpl implements ItemDao {
    @Override
    //暂时没有用
    public List<Item> queryAll(Integer pageNum, Integer pageRow) throws SQLException {
        Connection conn = null;
        List<Item> list = new ArrayList<Item>();
      
        try {
            conn = C3P0Util.getConnection();
            String sql = " select  t_item.*, t_level.name , t_syspara.name , t_type.name"
                    + " from  t_item "
                    + " left join t_level on item.id = t_level.id "
                    + " left join t_syspara on item.syspara_id = t_syspara.id"
                    + " left join t_type on item.type_id = t_type_id"
                    + " order by pro.id asc "
                    + " limit ?, ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, (pageNum - 1) * pageRow);
            ps.setInt(2, pageRow);

            ResultSet rs = ps.executeQuery();
            setItemList(list, rs);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
        	C3P0Util.close(conn);
        }
        return list;
    }
    //给ItemList赋值
    private void setItemList(List<Item> list, ResultSet rs) throws SQLException {
        Item item;
        while( rs.next() ){
            item = new Item();
            item.setId( rs.getInt("id") );
            item.setName(rs.getString("name"));
            item.setLevel_id(rs.getInt("level_id"));
            item.setSponsor(rs.getString("sponsor"));
            item.setSyspara_id(rs.getInt("syspara_id"));
            item.setBegintime(rs.getString("begintime"));
            item.setEndtime(rs.getString("endtime"));
            item.setType_id(rs.getInt("type_id"));
            list.add(item);
        }
    }

    @Override

    public boolean addItem(Item item) throws SQLException {

        Connection conn = null;

        int flag = 0;
        try {
            conn = C3P0Util.getConnection();

            String sql = "INSERT INTO t_item ( name , level_id , sponsor , syspara_id , begintime , endtime , type_id) "
             + "VALUES (? , ? , ? , ? ,  ? , ? , ?)";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,item.getName());
            ps.setInt(2,item.getLevel_id());
            ps.setString(3,item.getSponsor());
            ps.setInt(4,item.getSyspara_id());
            ps.setString(5,item.getBegintime());
            ps.setString(6,item.getEndtime());
            ps.setInt(7,item.getType_id());

            flag = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return flag==0?false:true;
    }

    @Override
    public boolean deleteItem(Item item) throws SQLException {

        Connection conn = null;

        int flag = 0;
        try {

            conn = C3P0Util.getConnection();
            String sql = "delete from t_item where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,1);
            flag = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return flag==0?false:true;
    }

    @Override
    public boolean updateItem(Item item) throws SQLException {
        Connection conn = null;
        int flag = 0;

        try {

            conn = C3P0Util.getConnection();
            String sql = "update t_item set name=? , level_id=? ,  sponsor=? ," +
                    " syspara_id=? , begintime=? , endtime=? , type_id=? where id=?";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,item.getName());
            ps.setInt(2,item.getLevel_id());
            ps.setString(3,item.getSponsor());
            ps.setInt(4,item.getSyspara_id());
            ps.setString(5,item.getBegintime());
            ps.setString(6,item.getEndtime());
            ps.setInt(7,item.getType_id());
            ps.setInt(8,item.getId());

            System.out.println(sql);
            flag = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return flag==0?false:true;
    }

    @Override
    public List<Item> queryItem(Item item) throws SQLException {
        Connection conn = null;
        List<Item> list = new ArrayList<Item>();
        //创建一个ArrayList，用来装载参数值
        List<String> buf = new ArrayList<String>();
       
        try {

            conn = C3P0Util.getConnection();
            String sql = "select * from t_item where 1=1";

            //id 查找

            if(item.getId() !=0 ){
                sql += " and id like ?";
                buf.add(item.getId()+"");
            }
            //名字 查找

            if(item.getName() !="" ){
                sql += " and name like ?";
                buf.add("%"+item.getName()+"%");
            }
            //等级id 查找
            if(item.getLevel_id() !=0 ){
                sql += " and level_id like ?";
                buf.add(item.getLevel_id()+"");
            }
            //赞助商查找
            if(item.getSponsor() !="" ){
                sql += " and sponsor like ?";
                buf.add("%"+item.getSponsor()+"%");
            }
            //系统参数id 查找
            if(item.getSyspara_id() !=0 ){
                sql += " and syspara_id like ?";
                buf.add(item.getSyspara_id()+"");
            }
            //时间查找
            if(item.getBegintime() != "2000-01-01"&&item.getEndtime() != "2000-01-01"){
                sql += " and begintime between cast(? as datetime) and  cast(? as datetime) " +
                        "and endtime between cast(? as datetime) and cast(? as datetime)";
                buf.add(item.getBegintime()+"");
                buf.add(item.getEndtime()+"");
                buf.add(item.getBegintime()+"");
                buf.add(item.getEndtime()+"");
            }
            //类型 id  查找
            if(item.getType_id() !=0 ) {
                sql += " and type_id like ?";
                buf.add(item.getType_id() + "");
            }

            PreparedStatement ps = conn.prepareStatement(sql);
            System.out.println(sql);
            for (int i = 0;i<buf.size();i++) {
                ps.setString(i+1, buf.get(i));
            }
            ResultSet rs = ps.executeQuery();
            setItemList(list, rs);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return list;
    }
}
